﻿using System.Data.SQLite;
using ZPeng.CSharpPro.DAL.DataBase;

namespace ZPeng.CSharpPro.Model
{
    public class ThingManagementInFactory : IDisposable
    {
        //设计
        // 获取工厂列表，
        // 获取物资种类列表

        //规划：2小时开发，1小时测试，开始时间14：40. 开始测试16：05,完成：16：44. 用时：开发：1小时25分，测试用时：40分钟
        public ThingManagementInFactory()
        {
            _database = new SQLiteDatabase(DefaultDBFilePath);
            _database.Open();
        }
        public void Dispose()
        {
            _database.Close();
        }

        public static void RollBackToDefaultData()
        {
            SQLiteDatabase database = new SQLiteDatabase(DefaultDBFilePath);
            database.RollBackToDefaultData();
        }


        public List<FactoryObj> GetFactoryList()
        {
            List<FactoryObj> factoryList = new List<FactoryObj>();


            SQLiteDataReader reader = _database.ExecuteReader("SELECT No, Name FROM FactoryList;");
            while (reader.Read())
            {
                string no = reader.GetString(0);
                string name = reader.GetString(1);
                factoryList.Add(new FactoryObj() { No = no, Name = name, ThingList = null });
            }
            return factoryList;
        }

        public List<ThingObj> GetThingList()
        {
            List<ThingObj> thingList = new List<ThingObj>();

            SQLiteDataReader reader = _database.ExecuteReader("SELECT No, Type, Count, FactoryNo FROM ThingList;");
            while (reader.Read())
            {
                string no = reader.GetString(0);
                string type = reader.GetString(1);
                int count = reader.GetInt32(2);
                string factoryNo = reader.GetString(3);
                thingList.Add(new ThingObj() { No = no, Type = type, Count = count, OwnerNo = factoryNo });
            }
            return thingList;
        }

        // 获取指定工厂的物资详细情况
        public List<ThingObj> GetThingListForTheFactory(string factoryNo)
        {
            List<ThingObj> thingList = new List<ThingObj>();

            SQLiteDataReader reader = _database.ExecuteReader($"SELECT No, Type,Count,FactoryNo FROM ThingList WHERE FactoryNo==\'{factoryNo}\';");
            while (reader.Read())
            {
                string no = reader.GetString(0);
                string type = reader.GetString(1);
                int count = reader.GetInt32(2);
                //string factoryNo = reader.GetString(3);
                thingList.Add(new ThingObj() { No = no, Type = type, Count = count, OwnerNo = factoryNo });
            }
            return thingList;
        }
        // 获取所有工厂的物资统计情况
        public List<FactoryObj> GetFactoryListWithAllThing()
        {
            List<FactoryObj> factoryList = GetFactoryList();
            List<ThingObj> thingList = GetThingList();

            foreach (var item in factoryList)
            {
                // linq_Type1 ：
                // item.ThingList = thingList.Where(t => t.OwnerNo == item.No).ToList();
                // linq_Type2:
                item.ThingList = (
                                    from t in thingList
                                    where t.OwnerNo == item.No
                                    select t
                                  ).ToList();
            }
            /* 交叉联合（CROSS JOIN：两个表的笛卡尔乘积，返回行数为x*y。
             * 这里返回FactoryList中所有行，以及ThingList中所有行
             * 
             * SELECT FactoryList.No, Name, ThingList.Type FROM FactoryList CROSS JOIN ThingList
             */
            /* 内连接（INNER JOIN：找到所有满足连接谓词的行的匹配对。
             * 这里返回FactoryList中能匹配的行，以及ThingList中能匹配的行
             * 
             *  SELECT Name, ThingList.Type, ThingList.count
                FROM FactoryList 
                INNER JOIN ThingList
                ON FactoryList.No = ThingList.FactoryNo
                order by Name
             */
            /* 左外连接（LEFT OUTER JOIN）（SQLite 只支持 左外连接）：在内连接的基础上，将表中任何未连接的行合并进来，用 NULL 值替代未匹配的值，
             *  这里返回FactoryList所有行，ThingList中能匹配的行
             *  SELECT Name, ThingList.Type, ThingList.count 
                FROM FactoryList 
                LEFT OUTER JOIN ThingList
                ON FactoryList.No = ThingList.FactoryNo
                order by Name;
             */
            return factoryList;
        }

        #region private
        private SQLiteDatabase _database;
        private static string DefaultDBFilePath = "database.sqlite";
        #endregion 
    }

    public class ThingObj
    {
        public string No = "";
        public string Type = "";
        public int Count;
        public string OwnerNo = "";

        public override string ToString()
        {
            return $"{No},{Type},{Count},{OwnerNo}";
        }
    }

    public class FactoryObj
    {
        public string No = "";
        public string Name = "";
        public required List<ThingObj>? ThingList;
        public override string ToString()
        {
            if (ThingList == null || ThingList.Count == 0)
                return $"{No},{Name}";
            else
                return $"{No},{Name}, ThingList:(" + string.Join("),(", ThingList) + ")";
        }
    }
}
